import 'dart:math';

import 'package:bpb/framework/utils/date_utils.dart';
import 'package:flutter/cupertino.dart';
import 'package:intl/intl.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class DBHelper {
  static String dbName = "db.db";
  static Database? database;

  static openDb() {
    if (database == null) {
      _init();
    }
  }

  ///添加7天的测试数据
  static Future<void> insertMockDataForLast7Days(int dayRange) async {
    final now = DateTime.now();
    final random = Random();

    for (int i = 0; i < dayRange; i++) {
      final date = now.subtract(Duration(days: i));
      final formattedDate = DateFormat('yyyy-MM-dd').format(date);
      final time = DateFormat('yyyy-MM-dd HH:mm:dd').format(date);
      // 随机生成 sys/dia/heart 数值（符合一般人体范围）
      final sys = 100 + random.nextInt(40); // 100–139
      final dia = 60 + random.nextInt(20); // 60–79
      final pulse = 60 + random.nextInt(40); // 60–99

      insertDb(sys, dia, pulse, formattedDate, time, "", "", "");
    }
  }

  static Future<void> insertMockDataForLast7Days2(int dayRange) async {
    final now = DateTime.now();
    final random = Random();

    for (int i = 0; i < dayRange; i++) {
      final date = now.add(Duration(days: i));
      final formattedDate = DateFormat('yyyy-MM-dd').format(date);
      final time = DateFormat('yyyy-MM-dd HH:mm:dd').format(date);
      // 随机生成 sys/dia/heart 数值（符合一般人体范围）
      final sys = 100 + random.nextInt(40); // 100–139
      final dia = 60 + random.nextInt(20); // 60–79
      final pulse = 60 + random.nextInt(40); // 60–99
      // String measurement_site,String symptoms,String medication

      String measurement_site = "";
      String symptoms = "";
      String medication = "";

      insertDb(sys, dia, pulse, formattedDate, time, measurement_site, symptoms, medication);
    }
  }

  ///统计所有条数
  static Future<int> getBpRecordCount() async {
    final List<Map<String, Object?>>? result = await database?.rawQuery('SELECT COUNT(*) as count FROM bp');
    if (result != null && result.isNotEmpty) {
      return (result.first['count'] as int?) ?? 0;
    }
    return 0;
  }

  ///按天查询
  static Future<List<Map<String, Object?>>> getRecordsByDate(String dayStr) async {
    final List<Map<String, Object?>>? result = await database?.rawQuery(
      '''
    SELECT * FROM bp
    WHERE date(recordTime) = ?
    ORDER BY recordTime DESC
  ''',
      [dayStr],
    );
    return result ?? [];
  }

  static Future<List<Map<String, Object?>>> getLastYearMonthlyStats() async {
    final sql = '''
    SELECT 
      recordTime,
      substr(date, 1, 7) AS month,
      AVG(sys) AS sys,
      AVG(dia) AS dia,
      AVG(pulse) AS pulse
    FROM bp
    WHERE date >= date('now', '-1 year')
    GROUP BY month
    ORDER BY month
  ''';

    List<Map<String, Object?>>? result = await database?.rawQuery(sql);
    return result ?? [];
  }

  ///最近90天
  static Future<List<Map<String, Object?>>> getLast90DaysAverage() async {
    List<Map<String, Object?>>? result = await database?.rawQuery('''
    SELECT 
      date,
      recordTime,
      AVG(sys) AS sys,
      AVG(dia) AS dia,
      AVG(pulse) AS pulse
    FROM bp
    WHERE date >= date('now', '-89 days') AND date <= date('now')
    GROUP BY date
    ORDER BY date ASC;
  ''');
    return result ?? [];
  }

  static Future<List<Map<String, Object?>>> get12WeeksAverage({int offset = 0}) async {
    final int totalWeeks = 12;
    final int startOffsetDays = (offset * totalWeeks * 7);

    final List<Map<String, Object?>>? result = await database?.rawQuery(
      '''
    WITH RECURSIVE weeks(week_index, start_date, end_date) AS (
      SELECT
        0,
        date('now', ? || ' days', 'weekday 1'),
        date('now', ? || ' days', 'weekday 0')
      UNION ALL
      SELECT
        week_index + 1,
        date('now', (? + (week_index + 1) * 7) || ' days', 'weekday 1'),
        date('now', (? + (week_index + 1) * 7) || ' days', 'weekday 0')
      FROM weeks
      WHERE week_index < ${totalWeeks - 1}
    )
    SELECT 
      week_index,
      start_date,
      end_date,
      IFNULL(ROUND(AVG(bp.sys), 1), 0) AS sys,
      IFNULL(ROUND(AVG(bp.dia), 1), 0) AS dia,
      IFNULL(ROUND(AVG(bp.pulse), 1), 0) AS pulse
    FROM weeks
    LEFT JOIN bp ON date(substr(bp.recordTime, 1, 10)) BETWEEN start_date AND end_date
    GROUP BY week_index
    ORDER BY start_date ASC;
  ''',
      [
        startOffsetDays.toString(), // 用于初始化第一周开始日期
        startOffsetDays.toString(), // 第一周结束日期
        startOffsetDays.toString(), // 后续周开始日期
        startOffsetDays.toString(), // 后续周结束日期
      ],
    );

    return result ?? [];
  }

  static Future<List<Map<String, Object?>>> getLast12WeeksAverage() async {
    List<Map<String, Object?>>? result = await database?.rawQuery('''
    WITH weeks(week_index, start_date, end_date) AS (
      SELECT 0, date('now', '-0 days', 'weekday 0', '-6 days'), date('now', '-0 days', 'weekday 0')
      UNION ALL
      SELECT 1, date('now', '-7 days', 'weekday 0', '-6 days'), date('now', '-7 days', 'weekday 0')
      UNION ALL
      SELECT 2, date('now', '-14 days', 'weekday 0', '-6 days'), date('now', '-14 days', 'weekday 0')
      UNION ALL
      SELECT 3, date('now', '-21 days', 'weekday 0', '-6 days'), date('now', '-21 days', 'weekday 0')
      UNION ALL
      SELECT 4, date('now', '-28 days', 'weekday 0', '-6 days'), date('now', '-28 days', 'weekday 0')
      UNION ALL
      SELECT 5, date('now', '-35 days', 'weekday 0', '-6 days'), date('now', '-35 days', 'weekday 0')
      UNION ALL
      SELECT 6, date('now', '-42 days', 'weekday 0', '-6 days'), date('now', '-42 days', 'weekday 0')
      UNION ALL
      SELECT 7, date('now', '-49 days', 'weekday 0', '-6 days'), date('now', '-49 days', 'weekday 0')
      UNION ALL
      SELECT 8, date('now', '-56 days', 'weekday 0', '-6 days'), date('now', '-56 days', 'weekday 0')
      UNION ALL
      SELECT 9, date('now', '-63 days', 'weekday 0', '-6 days'), date('now', '-63 days', 'weekday 0')
      UNION ALL
      SELECT 10, date('now', '-70 days', 'weekday 0', '-6 days'), date('now', '-70 days', 'weekday 0')
      UNION ALL
      SELECT 11, date('now', '-77 days', 'weekday 0', '-6 days'), date('now', '-77 days', 'weekday 0')
    )
    SELECT 
      week_index,
      start_date,
      end_date,
      IFNULL(ROUND(AVG(bp.sys), 1), 0) AS sys,
      IFNULL(ROUND(AVG(bp.dia), 1), 0) AS dia,
      IFNULL(ROUND(AVG(bp.pulse), 1), 0) AS pulse
    FROM weeks
    LEFT JOIN bp ON date(substr(bp.recordTime, 1, 10)) BETWEEN start_date AND end_date
    GROUP BY week_index
    ORDER BY start_date ASC;
  ''');
    return result ?? [];
  }

  ///最近30天
  static Future<List<Map<String, Object?>>> getLast30DaysAverage() async {
    List<Map<String, Object?>>? result = await database?.rawQuery('''
    SELECT 
      recordTime,
      substr(recordTime, 1, 10) AS date,
      AVG(sys) AS sys,
      AVG(dia) AS dia,
      AVG(pulse) AS pulse
    FROM bp
    WHERE date(substr(recordTime, 1, 10)) >= date('now', '-29 days')
      AND date(substr(recordTime, 1, 10)) <= date('now')
    GROUP BY date
    ORDER BY date ASC;
  ''');
    return result ?? [];
  }

  ///仅仅适用于按季度统计，按周再进行统计
  static Future<List<Map<String, Object?>>> getWeeklyAverages(DateTime start, DateTime end) async {
    final dailyData = await getDailyAverages(start, end);
    final List<Map<String, Object?>> weeklyResult = [];

    DateTime current = start;
    int week = 1;

    while (!current.isAfter(end)) {
      DateTime weekStart = current.subtract(Duration(days: current.weekday - 1));
      DateTime weekEnd = weekStart.add(const Duration(days: 6));

      if (weekStart.isBefore(start)) weekStart = start;
      if (weekEnd.isAfter(end)) weekEnd = end;

      final weekDays = dailyData.where((entry) {
        final entryDate = DateTime.parse(entry['date'] as String);
        return !entryDate.isBefore(weekStart) && !entryDate.isAfter(weekEnd);
      }).toList();

      double sumSys = 0, sumDia = 0, sumPulse = 0;
      int validCount = 0;

      for (var day in weekDays) {
        final sys = (day['sys'] as num).toDouble();
        final dia = (day['dia'] as num).toDouble();
        final pulse = (day['pulse'] as num).toDouble();

        // 如果三项中有任意一项为0，则跳过
        if (sys == 0 || dia == 0 || pulse == 0) continue;

        sumSys += sys;
        sumDia += dia;
        sumPulse += pulse;
        validCount++;
      }

      weeklyResult.add({
        'start': weekStart,
        'end': weekEnd,
        'week': week,
        'sys': validCount > 0 ? (sumSys / validCount).toStringAsFixed(1) : 0,
        'dia': validCount > 0 ? (sumDia / validCount).toStringAsFixed(1) : 0,
        'pulse': validCount > 0 ? (sumPulse / validCount).toStringAsFixed(1) : 0,
      });

      current = weekEnd.add(const Duration(days: 1));
      week++;
    }

    return weeklyResult;
  }

  ///获取
  static Future<List<Map<String, Object?>>> getDailyAverages(DateTime start, DateTime end) async {
    String startDateStr = start.toIso8601String().split('T').first;
    String endDateStr = end.toIso8601String().split('T').first;

    // 查询已有数据的每日平均值
    final List<Map<String, Object?>>? dbResult = await database?.rawQuery(
      '''
    SELECT
      date(date) as day,
      AVG(sys) AS sys,
      AVG(dia) AS dia,
      AVG(pulse) AS pulse
    FROM bp
    WHERE date(date) BETWEEN ? AND ?
    GROUP BY day
    ORDER BY day ASC
  ''',
      [startDateStr, endDateStr],
    );

    // 将结果转为 Map，key 是日期字符串
    final Map<String, Map<String, Object?>> resultMap = {for (var row in (dbResult ?? [])) row['day'] as String: row};

    // 构造完整结果
    List<Map<String, Object?>> finalResult = [];
    DateTime current = start;

    int week = 1;
    while (!current.isAfter(end)) {
      String dayStr = current.toIso8601String().split('T').first;
      String recordTime = DateUtil.DF_YMDHMS.format(current);

      if (resultMap.containsKey(dayStr)) {
        finalResult.add({
          "recordTime": recordTime,
          "date": dayStr,
          "week_${week}": week,
          'sys': resultMap[dayStr]?['sys'] ?? 0,
          'dia': resultMap[dayStr]?['dia'] ?? 0,
          'pulse': resultMap[dayStr]?['pulse'] ?? 0,
        });
      } else {
        finalResult.add({"recordTime": recordTime, "date": dayStr, "week_${week}": week, 'sys': 0, 'dia': 0, 'pulse': 0});
      }

      current = current.add(const Duration(days: 1));
      week++;
    }

    return finalResult;
  }

  ///最近一周
  static Future<List<Map<String, Object?>>> getLast7DaysDailyAverages() async {
    final db = database;
    if (db == null) {
      throw Exception('Database is not initialized');
    }

    var result = await db.rawQuery('''
    SELECT 
      date,
      recordTime,
      AVG(sys) AS sys,
      AVG(dia) AS dia,
      AVG(pulse) AS pulse
    FROM bp
    WHERE date >= date('now', '-6 days') AND date <= date('now')
    GROUP BY date
    ORDER BY date;
  ''');

    return result;
  }

  ///按天的平均统计
  static Future<List<Map<String, Object?>>> getDailyAveragesByDateRange(String startDate, String endDate) async {
    List<Map<String, Object?>>? result = await database?.rawQuery(
      '''
    SELECT 
      date,
      recordTime,
      AVG(sys) AS sys,
      AVG(dia) AS dia,
      AVG(pulse) AS pulse
    FROM bp
    WHERE date BETWEEN ? AND ?
    GROUP BY date
    ORDER BY date DESC;
  ''',
      [startDate, endDate],
    );

    return result ?? [];
  }

  static Future<int> countRecords() async {
    List<Map<String, Object?>>? result = await database?.rawQuery("SELECT COUNT(*) as count FROM bp;");
    if (result != null && result.isNotEmpty) {
      return result.first['count'] as int;
    } else {
      return 0;
    }
  }

  static insertDb(int sys, int dia, int heart, String date, String time, String measurement_site, String symptoms, String medication) async {
    debugPrint("insert ===>> ${sys}-> ${dia} --> ${heart}  -> ${date}  ->${time} -> ${measurement_site} -> ${symptoms} -> ${medication}");
    await database?.transaction((txn) async {
      int id1 = await txn.rawInsert('INSERT INTO bp(sys, dia,pulse,date,recordTime,measurement_site,symptoms,medication) VALUES(?, ?, ?,?,?,?,?,?)', [
        sys,
        dia,
        heart,
        date,
        time,
        measurement_site,
        symptoms,
        medication,
      ]);
    });
    debugPrint("插入成功");
  }

  static  updateBpById({
    required int autoId,
    required int sys,
    required int dia,
    required int heart,
  }) async {
    return await database?.update(
      'bp',
      {
        'sys': sys,
        'dia': dia,
        'pulse': heart,
      },
      where: 'autoId = ?',
      whereArgs: [autoId],
    );
  }



 static deleteBpById(int autoId) async {
    return await database?.delete(
      'bp',
      where: 'autoId = ?',
      whereArgs: [autoId],
    );
  }

  ///分页查询
  static queryByPage(int limit, int offset) async {
    List<Map<String, Object?>>? records = await database?.rawQuery("SELECT * FROM bp ORDER BY autoId LIMIT ? OFFSET ?;", [limit, offset]);
    return records ?? [];
  }

  static _init() async {
    var databasesPath = await getDatabasesPath();
    String path = join(databasesPath, dbName);
    database = await openDatabase(
      path,
      version: 1,
      onUpgrade: (Database db, int oldVersion, int newVersion) {},
      onCreate: (Database db, int version) async {
        await db.execute('''
  CREATE TABLE IF NOT EXISTS bp (
    autoId INTEGER PRIMARY KEY AUTOINCREMENT,
    sys INTEGER,
    dia INTEGER,
    pulse INTEGER,
    measurement_site TEXT,
    symptoms TEXT,
    medication TEXT,
    date TEXT,
    recordTime TEXT
  );
''');
      },
    );
    debugPrint("数据库初始化完毕");
  }

  static closedb() {
    database?.close();
  }
}
